using System;
using System.Data;
using System.Configuration;
using System.Collections.Generic;
using System.Data.SqlClient;
using AjaxNinja.Types;
using Snap.Diagnostics;

/// <summary>
/// Provides all SQL operations for AjaxNinja facebook applications
/// </summary>
namespace AjaxNinja.Data
{
    public class SQLDb
    {
        protected String connectionString;
        private Diagnostics dg;
        public SQLDb()
        {
            //Get the default connection string
            this.connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["fbConnectionString"].ConnectionString;
            dg = new Diagnostics("fbConnectionString");
        }

        public List<MadMessage> GetMessageHistory(int UserID)
        {
            return readDataItems<MadMessage>(SQLStrings.SQLgetMsgHistory, new string[] { "@UserID" }, new object[] { UserID });
        }

        public MadMessage GetLastMessage(int UserID)
        {
            return readDataItem<MadMessage>(SQLStrings.SQLgetLastMsg,
                new string[] { "@UserID" }, new object[] { UserID });
        }

        public MadMessage GetRandomSampleMessage()
        {
            return readDataItem<MadMessage>(SQLStrings.SQLgetRandomStartMsg,
                null, null);
        }

        public MadMessage GetSpecificMessage(int UserID, int StoryID)
        {
            return readDataItem<MadMessage>(SQLStrings.SQLgetSpecificItem,
                new string[] { "@UserID", "@StoryID" }, new object[] { UserID, StoryID });
        }

        public void AddMessage(string message, int userID)
        {
            this.writeDataItem(SQLStrings.SQLaddMadMsg,
                new string[] { "@UserID", "@MadText", "@MadTime" }, new object[] { userID, message, System.DateTime.Now });
        }

        public void AddUser(int userID)
        {
            this.writeDataItem(SQLStrings.SQLaddNewUser,
                new string[] { "@UserID" }, new object[] { userID });
        }

        public AppUser CheckUser(int userID)
        {
            return readDataItem<AppUser>(SQLStrings.SQLcheckUserExists,
                new string[] { "@UserID" }, new object[] { userID });
        }

        /// <summary>
        /// Executes a "write" query
        /// </summary>
        /// <param name="query">The parameterized query</param>
        /// <param name="paramnames">The parameter names</param>
        /// <param name="paramvalues">The parameter values</param>
        private void writeDataItem(string query, string[] paramnames, object[] paramvalues)
        {
            using (SqlConnection cnn = new SqlConnection(this.connectionString))
            {
                SqlCommand cmd = new SqlCommand();
                cmd.CommandType = CommandType.Text;
                cmd.Connection = cnn;
                cmd.CommandText = query;

                if (paramnames != null && paramnames.Length > 0)
                {
                    for (int i = 0; i < paramnames.Length; i++)
                    {
                        cmd.Parameters.AddWithValue(paramnames[i],
                            paramvalues[i]
                        );
                    }
                }

                try
                {
                    cnn.Open();
                    cmd.ExecuteNonQuery();
                }
                catch (SqlException er)
                {
                    SError logMsg = new SError("SQLDb.cs", query.Substring(0, 256), "", 0, er.Message + er.StackTrace);
                    dg.logError(logMsg);
                }
                catch (Exception er)
                {
                    SError logMsg = new SError("SQLDb.cs", er.TargetSite.Name, "", 0, er.Message + er.StackTrace);
                    dg.logError(logMsg);
                }
            }
        }

        /// <summary>
        /// Get a data item from the database
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="query"></param>
        /// <param name="paramnames"></param>
        /// <param name="paramvalues"></param>
        /// <returns></returns>
        private T readDataItem<T>(string query, string[] paramnames, object[] paramvalues)
            where T : IFacebookEntity, new()
        {
            T dataItem = default(T);
            using (SqlConnection cnn = new SqlConnection(this.connectionString))
            {
                SqlCommand cmd = new SqlCommand();
                cmd.CommandType = CommandType.Text;
                cmd.Connection = cnn;
                cmd.CommandText = query;

                if (paramnames != null && paramnames.Length > 0)
                {
                    for (int i = 0; i < paramnames.Length; i++)
                    {
                        cmd.Parameters.AddWithValue(paramnames[i],
                            paramvalues[i]
                        );
                    }
                }

                try
                {
                    cnn.Open();
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader != null && reader.HasRows)
                        {
                            reader.Read();
                            dataItem = new T();
                            dataItem.Fill(reader);
                        }
                    }

                    return dataItem;
                }
                catch (SqlException er)
                {
                    SError logMsg = new SError("SQLDb.cs", query.Substring(0, 256), "", 0, er.Message + er.StackTrace);
                    dg.logError(logMsg);
                }
                catch (Exception er)
                {
                    SError logMsg = new SError("SQLDb.cs", er.TargetSite.Name, "", 0, er.Message + er.StackTrace);
                    dg.logError(logMsg);
                }
            }

            return dataItem;
        }

        /// <summary>
        /// Get several data items from the database
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="query"></param>
        /// <param name="paramnames"></param>
        /// <param name="paramvalues"></param>
        /// <returns></returns>
        private List<T> readDataItems<T>(string query, string[] paramnames, object[] paramvalues)
            where T : IFacebookEntity, new()
        {
            List<T> items = new List<T>();
            using (SqlConnection cnn = new SqlConnection(this.connectionString))
            {
                SqlCommand cmd = new SqlCommand();
                cmd.CommandType = CommandType.Text;
                cmd.Connection = cnn;
                cmd.CommandText = query;

                if (paramnames != null && paramnames.Length > 0)
                {
                    for (int i = 0; i < paramnames.Length; i++)
                    {
                        cmd.Parameters.AddWithValue(paramnames[i],
                            paramvalues[i]
                        );
                    }
                }
     
                try
                {
                    cnn.Open();
                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader != null && reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                T dataItem = new T();
                                dataItem.Fill(reader);
                                items.Add(dataItem);

                            }
                        }
                    }
                }
                catch (SqlException er)
                {
                    SError logMsg = new SError("SQLDb.cs", query.Substring(0, 256), "", 0, er.Message + er.StackTrace);
                    dg.logError(logMsg);
                }
                catch (Exception er)
                {
                    SError logMsg = new SError("SQLDb.cs", er.TargetSite.Name, "", 0, er.Message + er.StackTrace);
                    dg.logError(logMsg);
                }
            }

            return items;
        }
    }
}